USE [SI2Final]
GO

/****** Object:  Table [dbo].[Consulta]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Consulta](
	[IdConsulta] [int] IDENTITY(1,1) NOT NULL,
	[IdMotivoConsulta] [int] NOT NULL,
	[Data] [date] NULL,
	[DataRegisto] [datetime] NULL,
	[IdMedico] [nvarchar](20) NOT NULL,
	[IdPaciente] [int] NOT NULL,
	[IdEspecialidade] [int] NOT NULL,
 CONSTRAINT [PK_Consulta] PRIMARY KEY CLUSTERED 
(
	[IdConsulta] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Especialidade]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Especialidade](
	[IdEspecialidade] [int] IDENTITY(1,1) NOT NULL,
	[Nome] [nvarchar](300) NULL,
	[Preco] [decimal](10, 2) NULL,
 CONSTRAINT [PK_Especialidade] PRIMARY KEY CLUSTERED 
(
	[IdEspecialidade] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[EspecialidadeMedico]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[EspecialidadeMedico](
	[IdEspecialidade] [int] NOT NULL,
	[IdMedico] [nvarchar](20) NOT NULL
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Exame]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Exame](
	[IdExame] [int] IDENTITY(1,1) NOT NULL,
	[Notas] [nvarchar](max) NULL,
	[Resultado] [nvarchar](max) NULL,
	[IdEquipamento] [nchar](10) NULL,
 CONSTRAINT [PK_Exame] PRIMARY KEY CLUSTERED 
(
	[IdExame] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Fatura]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Fatura](
	[IdFatura] [int] NOT NULL,
	[Ano] [int] NOT NULL,
	[Data] [datetime] NULL,
	[Morada] [nvarchar](2000) NULL,
	[Nome] [nvarchar](1250) NULL,
	[Nif] [int] NULL,
 CONSTRAINT [PK_Fatura] PRIMARY KEY CLUSTERED 
(
	[IdFatura] ASC,
	[Ano] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[ItemFatura]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ItemFatura](
	[Numero] [int] NOT NULL,
	[Descricao] [nvarchar](1000) NULL,
	[Montante] [decimal](10, 2) NULL,
	[IdFatura] [int] NOT NULL,
	[Ano] [int] NOT NULL,
 CONSTRAINT [PK_ItemFatura] PRIMARY KEY CLUSTERED 
(
	[Numero] ASC,
	[IdFatura] ASC,
	[Ano] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Medicamento]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Medicamento](
	[IdMedicamento] [int] IDENTITY(1,1) NOT NULL,
	[PrincipioActivo] [nvarchar](300) NOT NULL,
	[NomeComercial] [nvarchar](300) NULL,
	[Laboratorio] [nvarchar](300) NULL,
	[Dose] [decimal](4, 2) NULL,
 CONSTRAINT [PK_Medicamento] PRIMARY KEY CLUSTERED 
(
	[IdMedicamento] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[MedicamentoPaciente]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MedicamentoPaciente](
	[IdMedicamento] [int] NOT NULL,
	[IdPaciente] [int] NOT NULL,
	[IdPosologia] [int] NOT NULL
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Medico]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Medico](
	[LicencaMedica] [nvarchar](20) NOT NULL,
	[DataLicenca] [date] NOT NULL,
	[NumeroPacientesDiario] [int] NULL,
	[NumeroListadeEspera] [int] NULL,
	[IdPessoa] [int] NOT NULL,
 CONSTRAINT [PK_Medico] PRIMARY KEY CLUSTERED 
(
	[LicencaMedica] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Morada]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Morada](
	[Ordem] [smallint] NOT NULL,
	[Rua] [nvarchar](1000) NULL,
	[Numero] [nvarchar](9) NULL,
	[CodigoPostar] [nvarchar](8) NULL,
	[Cidade] [nvarchar](300) NULL,
	[Pais] [nvarchar](300) NULL,
	[IdPessoa] [int] NOT NULL,
	[IdTipoContacto] [int] NOT NULL,
 CONSTRAINT [PK_Morada] PRIMARY KEY CLUSTERED 
(
	[Ordem] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[MotivoConsulta]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MotivoConsulta](
	[IdMotivoConsulta] [int] IDENTITY(1,1) NOT NULL,
	[Descricao] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_MotivoConsulta] PRIMARY KEY CLUSTERED 
(
	[IdMotivoConsulta] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Paciente]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Paciente](
	[NumeroBenefeciario] [int] NOT NULL,
	[SistemaSaude] [nvarchar](200) NULL,
	[IdPessoa] [int] NOT NULL,
 CONSTRAINT [PK_Paciente] PRIMARY KEY CLUSTERED 
(
	[NumeroBenefeciario] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Pessoa]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Pessoa](
	[Nif] [int] NULL,
	[NumeroSS] [int] NOT NULL,
	[Nome] [nvarchar](1000) NULL,
	[UltimoNome] [nvarchar](250) NULL,
	[DataNascimento] [date] NULL,
	[Nacionalidade] [nvarchar](300) NULL,
	[EMail] [nvarchar](1000) NULL,
 CONSTRAINT [PK_Pessoa] PRIMARY KEY CLUSTERED 
(
	[NumeroSS] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Posologia]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Posologia](
	[IdPosologia] [int] NOT NULL,
	[Descricao] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Posologia] PRIMARY KEY CLUSTERED 
(
	[IdPosologia] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Relatorio]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Relatorio](
	[IdRelatorio] [int] IDENTITY(1,1) NOT NULL,
	[Data] [datetime] NULL,
	[Descricao] [nvarchar](max) NULL,
	[IdConsulta] [int] NOT NULL,
 CONSTRAINT [PK_Relatorio] PRIMARY KEY CLUSTERED 
(
	[IdRelatorio] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[RelatorioExame]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RelatorioExame](
	[IdRelatorio] [int] NULL,
	[IdEquipamento] [int] NULL,
	[Notas] [nvarchar](max) NULL
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[RelatorioMedico]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RelatorioMedico](
	[IdRelatorio] [int] NULL,
	[EstadoClinico] [nvarchar](max) NULL,
	[Prescricoes] [nvarchar](max) NULL
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[RelatorioMensalFinanceiro]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RelatorioMensalFinanceiro](
	[Ano] [int] NOT NULL,
	[Mes] [int] NOT NULL,
	[Relatorio] [xml] NULL,
 CONSTRAINT [PK_RelatorioMensalFinanceiro] PRIMARY KEY CLUSTERED 
(
	[Ano] ASC,
	[Mes] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[Telefone]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Telefone](
	[Ordem] [int] NOT NULL,
	[Numero] [nvarchar](15) NULL,
	[IdPessoa] [int] NOT NULL,
	[IdTipoContacto] [int] NOT NULL,
 CONSTRAINT [PK_Telefone] PRIMARY KEY CLUSTERED 
(
	[Ordem] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[TipoContacto]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TipoContacto](
	[Tipo] [int] NOT NULL,
	[Descricao] [nvarchar](250) NULL,
 CONSTRAINT [PK_TipoContacto] PRIMARY KEY CLUSTERED 
(
	[Tipo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [SI2Final]
GO

/****** Object:  Table [dbo].[TipoExame]    Script Date: 02/20/2012 17:26:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TipoExame](
	[IdTipoExame] [int] NULL,
	[Nome] [nvarchar](500) NULL,
	[Preco] [decimal](10, 2) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Consulta]  WITH CHECK ADD  CONSTRAINT [FK_Consulta_Especialidade] FOREIGN KEY([IdEspecialidade])
REFERENCES [dbo].[Especialidade] ([IdEspecialidade])
GO

ALTER TABLE [dbo].[Consulta] CHECK CONSTRAINT [FK_Consulta_Especialidade]
GO

ALTER TABLE [dbo].[Consulta]  WITH CHECK ADD  CONSTRAINT [FK_Consulta_Medico] FOREIGN KEY([IdMedico])
REFERENCES [dbo].[Medico] ([LicencaMedica])
GO

ALTER TABLE [dbo].[Consulta] CHECK CONSTRAINT [FK_Consulta_Medico]
GO

ALTER TABLE [dbo].[Consulta]  WITH CHECK ADD  CONSTRAINT [FK_Consulta_MotivoConsulta] FOREIGN KEY([IdMotivoConsulta])
REFERENCES [dbo].[MotivoConsulta] ([IdMotivoConsulta])
GO

ALTER TABLE [dbo].[Consulta] CHECK CONSTRAINT [FK_Consulta_MotivoConsulta]
GO

ALTER TABLE [dbo].[Consulta]  WITH CHECK ADD  CONSTRAINT [FK_Consulta_Paciente] FOREIGN KEY([IdPaciente])
REFERENCES [dbo].[Paciente] ([NumeroBenefeciario])
GO

ALTER TABLE [dbo].[Consulta] CHECK CONSTRAINT [FK_Consulta_Paciente]
GO

ALTER TABLE [dbo].[EspecialidadeMedico]  WITH CHECK ADD  CONSTRAINT [FK_EspecialidadeMedico_Especialidade] FOREIGN KEY([IdEspecialidade])
REFERENCES [dbo].[Especialidade] ([IdEspecialidade])
GO

ALTER TABLE [dbo].[EspecialidadeMedico] CHECK CONSTRAINT [FK_EspecialidadeMedico_Especialidade]
GO

ALTER TABLE [dbo].[EspecialidadeMedico]  WITH CHECK ADD  CONSTRAINT [FK_EspecialidadeMedico_Medico] FOREIGN KEY([IdMedico])
REFERENCES [dbo].[Medico] ([LicencaMedica])
GO

ALTER TABLE [dbo].[EspecialidadeMedico] CHECK CONSTRAINT [FK_EspecialidadeMedico_Medico]
GO

ALTER TABLE [dbo].[ItemFatura]  WITH CHECK ADD  CONSTRAINT [FK_ItemFatura_Fatura] FOREIGN KEY([IdFatura], [Ano])
REFERENCES [dbo].[Fatura] ([IdFatura], [Ano])
GO

ALTER TABLE [dbo].[ItemFatura] CHECK CONSTRAINT [FK_ItemFatura_Fatura]
GO

ALTER TABLE [dbo].[MedicamentoPaciente]  WITH CHECK ADD  CONSTRAINT [FK_MedicamentoPaciente_Medicamento] FOREIGN KEY([IdMedicamento])
REFERENCES [dbo].[Medicamento] ([IdMedicamento])
GO

ALTER TABLE [dbo].[MedicamentoPaciente] CHECK CONSTRAINT [FK_MedicamentoPaciente_Medicamento]
GO

ALTER TABLE [dbo].[MedicamentoPaciente]  WITH CHECK ADD  CONSTRAINT [FK_MedicamentoPaciente_Paciente] FOREIGN KEY([IdPaciente])
REFERENCES [dbo].[Paciente] ([NumeroBenefeciario])
GO

ALTER TABLE [dbo].[MedicamentoPaciente] CHECK CONSTRAINT [FK_MedicamentoPaciente_Paciente]
GO

ALTER TABLE [dbo].[MedicamentoPaciente]  WITH CHECK ADD  CONSTRAINT [FK_MedicamentoPaciente_Posologia] FOREIGN KEY([IdPosologia])
REFERENCES [dbo].[Posologia] ([IdPosologia])
GO

ALTER TABLE [dbo].[MedicamentoPaciente] CHECK CONSTRAINT [FK_MedicamentoPaciente_Posologia]
GO

ALTER TABLE [dbo].[Medico]  WITH CHECK ADD  CONSTRAINT [FK_Medico_Pessoa] FOREIGN KEY([IdPessoa])
REFERENCES [dbo].[Pessoa] ([NumeroSS])
GO

ALTER TABLE [dbo].[Medico] CHECK CONSTRAINT [FK_Medico_Pessoa]
GO

ALTER TABLE [dbo].[Morada]  WITH CHECK ADD  CONSTRAINT [FK_Morada_Pessoa] FOREIGN KEY([IdPessoa])
REFERENCES [dbo].[Pessoa] ([NumeroSS])
GO

ALTER TABLE [dbo].[Morada] CHECK CONSTRAINT [FK_Morada_Pessoa]
GO

ALTER TABLE [dbo].[Morada]  WITH CHECK ADD  CONSTRAINT [FK_Morada_TipoContacto] FOREIGN KEY([IdTipoContacto])
REFERENCES [dbo].[TipoContacto] ([Tipo])
GO

ALTER TABLE [dbo].[Morada] CHECK CONSTRAINT [FK_Morada_TipoContacto]
GO

ALTER TABLE [dbo].[Paciente]  WITH CHECK ADD  CONSTRAINT [FK_Paciente_Pessoa] FOREIGN KEY([IdPessoa])
REFERENCES [dbo].[Pessoa] ([NumeroSS])
GO

ALTER TABLE [dbo].[Paciente] CHECK CONSTRAINT [FK_Paciente_Pessoa]
GO

ALTER TABLE [dbo].[Relatorio]  WITH CHECK ADD  CONSTRAINT [FK_Relatorio_Consulta] FOREIGN KEY([IdConsulta])
REFERENCES [dbo].[Consulta] ([IdConsulta])
GO

ALTER TABLE [dbo].[Relatorio] CHECK CONSTRAINT [FK_Relatorio_Consulta]
GO

ALTER TABLE [dbo].[Telefone]  WITH CHECK ADD  CONSTRAINT [FK_Telefone_Pessoa] FOREIGN KEY([IdPessoa])
REFERENCES [dbo].[Pessoa] ([NumeroSS])
GO

ALTER TABLE [dbo].[Telefone] CHECK CONSTRAINT [FK_Telefone_Pessoa]
GO

ALTER TABLE [dbo].[Telefone]  WITH CHECK ADD  CONSTRAINT [FK_Telefone_TipoContacto] FOREIGN KEY([IdTipoContacto])
REFERENCES [dbo].[TipoContacto] ([Tipo])
GO

ALTER TABLE [dbo].[Telefone] CHECK CONSTRAINT [FK_Telefone_TipoContacto]
GO

